package Day6_SampleCodes_JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class SampleJDBC {

	
	private final static String url = "jdbc:mysql://localhost:3306/world";
	private final static String user = "root";
	private final static String password = "password";
	private final static String driver = "com.mysql.jdbc.Driver";

	public static void main(String[] args) {
		//retrieveName();
		insertIntoDB("ABC","Singapore", "Asia","Asia");
	}

	public Connection getConnection(String url, String user, String password,
			String driver) {
		Connection conn = null;

		try {
			// load driver class
			// if you are using any other database then load the driver here
			Class.forName(driver);
			// create the connection string using the static getConnection
			// method
			// change the connection string according to your db, ip, username
			// and password
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		return conn;

	}

	public static void deleteFromDB(String name){
		
	}
	
	public static void insertIntoDB(String countryCode, String countryName,
			String countryContinent, String countryRegion) {
		try {
			Connection conn = null;
			PreparedStatement ps = null;
			int insertSuccess = 0;

			if (conn == null) {
				SampleJDBC connMan = new SampleJDBC();
				conn = connMan.getConnection(url, user, password, driver);

			}

			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO country (Code, Name, Continent, Region) VALUES (?,?,?,?)");

			ps = conn.prepareStatement(sb.toString());
			ps.setString(1, countryCode);
			ps.setString(2, countryName);
			ps.setString(3, countryContinent);
			ps.setString(4, countryRegion);

			insertSuccess = ps.executeUpdate();
			System.out.println(insertSuccess);
			System.out.println(sb.toString());
		} catch (SQLException e) {
e.printStackTrace();
		}
	}

	public static void retrieveName() {
		Connection conn = null;
		PreparedStatement stmt = null;
		SampleJDBC connMan = new SampleJDBC();
		//List<UserName> listOfNames = null;
		String sql = ("select * from actor order by last_name");

		try {
			conn = connMan.getConnection(url, user, password, driver);
			stmt = conn.prepareStatement(sql);

			ResultSet rs = stmt.executeQuery(sql);
			//listOfNames = new ArrayList<UserName>();

			while (rs.next()) {
				//UserName userName = new UserName();
				// userName.setFirst_name(rs.getString("first_name"));
				// userName.setFirst_name(rs.getString("last_name"));
				System.out.println("Actor Names :   "
						+ rs.getString("first_name") + " "
						+ rs.getString("last_name"));
				// listOfNames.add(userName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}
